package org.misalen.web.database.repository.mysql;

import java.math.BigInteger;
import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.AliasToBeanResultTransformer;
import org.misalen.utils.TextUtil;
import org.misalen.web.database.dao.mysql.impl.BaseMySqlDaoImpl;
import org.misalen.web.database.model.mysql.pro.ProVideoReward;
import org.misalen.web.database.util.PaginationFrom;
import org.springframework.stereotype.Repository;

@Repository
public class ProVideoRewardRepository extends BaseMySqlDaoImpl<ProVideoReward> {

	public void saveList(int[] is, String videoId, long l, String timeLimit) {
		Date date = new Date();
		for (int i : is) {
			ProVideoReward proVideoReward = new ProVideoReward();
			proVideoReward.setPlayeTime(new BigInteger(l + ""));
			proVideoReward.setAddTime(date);
			proVideoReward.setMoney(i);
			proVideoReward.setVideoId(videoId);
			proVideoReward.setPlayedTime(new BigInteger("0"));
			proVideoReward.setTimeLimit(timeLimit);
			save(proVideoReward);
		}
	}

	public PaginationFrom<ProVideoReward> terminalList(String videoId, PaginationFrom<ProVideoReward> pagination) {
		Session session = this.openSession();
		String sql = "SELECT a.primaryKey primaryKey,b.userName userName,b.mobile,b.vehicleNumber,b.vehicleModel,a.getTime,a.money,a.playedTime,a.playeTime FROM ProVideoReward a LEFT JOIN ProDriverUser b ON a.userId=b.primaryKey "
				+ " LEFT JOIN ProVideoInfo c ON a.videoId = c.primaryKey WHERE b.primaryKey IS NOT NULL and a.videoId='"
				+ videoId + "' GROUP BY a.primaryKey order by a.getTime desc";
		SQLQuery query = session.createSQLQuery(sql);
		query.setResultTransformer(new AliasToBeanResultTransformer(ProVideoReward.class));
		@SuppressWarnings("unchecked")
		List<ProVideoReward> list = query.list();
		pagination.setTotal(Long.valueOf(session
				.createSQLQuery(
						"select count(*) from ProVideoReward where userId IS NOT NULL AND videoId='" + videoId + "'")
				.uniqueResult().toString()));
		setResultIndex(query, pagination.getPage(), pagination.getRows());
		pagination.setList(list);
		closeSession(session);
		session = this.openSession();
		return pagination;
	}

	public ProVideoReward getByVideoUser(String userId, String videoId) {
		return get("from ProVideoReward where userId=?0 and videoId=?1", new Object[] { userId, videoId });
	}

	public Long getValidCountByVideo(String videoId) {
		return count("select count(*) from ProVideoReward where userId IS NULL and videoId=?0",
				new Object[] { videoId });
	}

	public ProVideoReward getValidByVideo(String videoId) {
		return get("from ProVideoReward where userId IS NULL and videoId=?0", new Object[] { videoId });
	}

	@SuppressWarnings("unchecked")
	public PaginationFrom<ProVideoReward> getListByGet(String videoId) {
		PaginationFrom<ProVideoReward> paginationFrom = new PaginationFrom<>();
		Session session = this.openSession();
		SQLQuery query = session.createSQLQuery(
				"select A.*,B.userName,C.videoName from ProVideoReward A LEFT JOIN ProDriverUser B ON A.userId=B.primaryKey LEFT JOIN ProVideoInfo C ON A.videoId=C.primaryKey WHERE A.videoId='"
						+ videoId + "' and A.userId IS NOT NULL GROUP BY A.primaryKey ORDER BY A.money desc");
		query.setResultTransformer(new AliasToBeanResultTransformer(ProVideoReward.class));
		List<ProVideoReward> list = query.list();
		paginationFrom.setList(list);
		paginationFrom.setTotal((long) list.size());
		paginationFrom.setRows(list.size());
		paginationFrom.setPage(1);
		closeSession(session);
		return paginationFrom;
	}
	
	public void removeReward(String videoId) {
		Session session = this.openSession();
		String sql = "SELECT a.primaryKey primaryKey,a.money,a.userId FROM ProVideoReward a "
				+ " LEFT JOIN ProVideoInfo c ON a.videoId = c.primaryKey WHERE a.videoId='"
				+ videoId + "'";
		SQLQuery query = session.createSQLQuery(sql);
		query.setResultTransformer(new AliasToBeanResultTransformer(ProVideoReward.class));
		@SuppressWarnings("unchecked")
		List<ProVideoReward> list = query.list();
		if(list != null) {
			for(int i=0; i<list.size(); i++) {
				ProVideoReward pr = list.get(i);
				String strSql = "update ProDriverUser set freezeBalance=freezeBalance-" + pr.getMoney();
				strSql += " where primaryKey='" + pr.getUserId() + "'";
				Query updatePr = session.createQuery(strSql);
				updatePr.executeUpdate();
			}
		}
		
		closeSession(session);
	}
	
	public void deleteRewardByVideo(String videoId) {
		String sql = "delete from ProVideoReward where videoId = '" + videoId + "'";
		Session session = this.openSession();
		Query query = session.createSQLQuery(sql);
		query.executeUpdate();
		closeSession(session);
	}
	
	public void updateTimeLimit(String date) {
		String sql = "update ProVideoReward a  set a.timeLimit='" + TextUtil.booleanToString(true)
				+ "'  where a.timeLimit='" + TextUtil.booleanToString(false) + "' and videoId in "
				+ "(SELECT b.primaryKey FROM ProVideoInfo b where " + "b.startDate<=str_to_date('" + date
				+ "','%Y-%m-%d') " + "and b.stopDate>str_to_date('" + date + "','%Y-%m-%d') " + "and b.timeLimit='"
				+ TextUtil.booleanToString(false) + "')";
		Session session = this.openSession();
		Query query = session.createSQLQuery(sql);
		query.executeUpdate();
		closeSession(session);
		String sql2 = "update ProVideoReward a set a.timeLimit='" + TextUtil.booleanToString(false)
				+ "'  where a.timeLimit='" + TextUtil.booleanToString(true) + "' and videoId in "
				+ "(SELECT b.primaryKey FROM ProVideoInfo b where " + "(b.startDate>str_to_date('" + date
				+ "','%Y-%m-%d') " + "or b.stopDate<=str_to_date('" + date + "','%Y-%m-%d')) " + "and b.timeLimit='"
				+ TextUtil.booleanToString(true) + "')";
		Session session2 = this.openSession();
		Query query2 = session2.createSQLQuery(sql2);
		query2.executeUpdate();
		closeSession(session2);
	}

	public long getValidCountByUser(String userId, String type) {
		Session session = this.openSession();
		StringBuffer countBuffer = new StringBuffer();
		countBuffer.append(
				" select count(*) from ( SELECT count(*) from ProVideoReward B LEFT JOIN ProVideoInfo A ON A.primaryKey = B.videoId ");
		countBuffer.append(" WHERE B.userId='");
		countBuffer.append(userId);
		countBuffer.append("' and A.type='");
		countBuffer.append(type);
		countBuffer.append("' AND A.timeLimit='" + TextUtil.booleanToString(true) + "' GROUP BY B.primaryKey) T");
		long count = Long.valueOf(session.createSQLQuery(countBuffer.toString()).uniqueResult().toString());
		closeSession(session);
		return count;
	}

	public void updatePlayedTime(String userId, String videoId, Integer runningtime) {
		Session session = this.openSession();
		Query query = session.createQuery("update ProVideoReward set playedTime=" + runningtime + " where videoId='"
				+ videoId + "' and userId='" + userId + "'");
		query.executeUpdate();
		closeSession(session);
	}

}